[<<Previous Entry]
[^^Up^^]
[Next Entry>>]
[Menu]
[About The Guide]
Indexing on Variable Length Fields
There are several issues that must be addressed by the programmer in
order to create and maintain an index on part (or all) of a FlexFile
Variable Length Field.
First, creating an index is as easy as:
INDEX ON V_RET( vlf_fld, "ALIAS_1", 1, 15 ) TO NTX_FILE.NTX
This would create an index on the first fifteen characters of a FlexFile
VLF. However, every element of a clipper index must be the same length
and the above example does not guarantee this. (If V_RETRIEVE() returns
a string of less than 15 characters, you will corrupt your index.) So,
you should PAD() the return from V_RETRIEVE() with spaces as in:
INDEX ON PAD( V_RET(.....), 15 ) TO NTX_FILE.NTX
The second issue is to note that only data of the same type can be
indexed in this manner. Accordingly, make sure that the character data
is not binary data. If a CHR(0) is found in the first 15 characters of
the above example, Clipper will consider that to indicate the end of the
string. This will make the string's length different from other strings
in the index and, thereby, corrupt the index.
The last issue is described below, but is more complex. However, it is
not necessary to understand why, as long as you always abide by the
rule: You must always have a REPLACE command that does not have a
V_REPLACE in its syntax before REPLACEs that contain V_REPLACE(). For
example,
&& INCORRECT....will corrupt the index if the index relys on either
&& the data pointed to by vlf_1 or vlf_2.
REPLACE vlf_1 WITH V_REPLACE( "testing", vlf_1 )
REPLACE vlf_2 WITH V_REPLACE( "testing", vlf_2 )
SKIP
&& CORRECT....the first replace is a "dummy" but maintains integrity.
REPLACE any_fld WITH any_fld
REPLACE vlf_1 WITH V_REPLACE( "testing", vlf_1 )
REPLACE vlf_2 WITH V_REPLACE( "testing", vlf_2 )
SKIP
First, the only reason I placed two V_REPLACE()s in the example is to
show that only one "dummy" replace is necessary before any number of
imbedded V_REPLACE()s. Remember that anything that causes the index to
be updated (such as COMMIT, SKIP, SEEK, etc.) require that a new "dummy"
replace be used before attempting another REPLACE with imbedded
V_REPLACE()s. For example,
&& CORRECT
REPLACE any_fld WITH any_fld
REPLACE vlf_1 WITH V_REPLACE( "testing", vlf_1 )
REPLACE vlf_2 WITH V_REPLACE( "testing", vlf_2 )
SKIP
&& INCORRECT....will corrupt the index without another "dummy"
&& replace after the COMMIT. Assuming, of course, that vlf_2
&& is included in the index key.
REPLACE any_fld WITH any_fld
REPLACE vlf_1 WITH V_REPLACE( "testing", vlf_1 )
COMMIT
REPLACE vlf_2 WITH V_REPLACE( "testing", vlf_2 )
Ok, so what is going on? Understanding the sequence of events that
occurs during a REPLACE is critical to understanding the apparent
paradox in the examples above.
Remembering that the arguments of a function or command are all
evaluated before the function itself is called, the syntax
REPLACE vlf_fld WITH V_REP( "Testing an indexed replace", vlf_fld )
first executes FlexFile's V_REPLACE() and then executes Clipper's
REPLACE.
This affects indexing because Clipper always evaluates the index key
twice. Once at the begining of the first of one or more REPLACEs (to
remember which index key has to be removed from the index) and the
second after any of several "commit" commands such as COMMIT, SKIP,
SEEK, GOTO, etc. (to calculate and insert a new index key). However, if
FlexFile already deleted the old information pointed to by vlf_fld
before CLIPPER began to execute the REPLACE code, then the first
evaluation of the index key occurs after its data has already been
deleted by FlexFile.
I realize this is confusing so I will try again in steps:
+- step 2 +- step 1
| |
REPLACE vlf_fld WITH V_REPLACE( "testing", vlf_fld )
| |
+- step 4 +- step 3
step 5
1. FlexFile deletes the data associated with the pointer-field vlf_fld
that is in the file before the REPLACE.
2. FlexFile puts away the text "testing" in an appropriate location.
3. FlexFile returns a six byte pointer-field which is immediately passed
to REPLACE. This pointer-field is different than that of step 1 and
this new pointer-field is not yet in the file (it is being stored in
memory for the moment).
4. Clipper begins executing its REPLACE code with the string passed to
it from step 3.
5. Clipper evaluates the index and stores the evaluated value in a
buffer. This sets up the error. The index key should evaluate to the
data pointed to by the vlf_fld which is still in the file. However,
FlexFile deleted that data in step 1. Therefore, the V_RETRIEVE() in the
index key will return a null string and your PAD function will change
this to 15 bytes of spaces.
No Clipper error actually occurs until the first COMMIT, SKIP, SEEK,
etc. It is at this point that Clipper does the work on the index. The
first thing Clipper does is delete the old index key (which was put into
a memory buffer in step 5). However, Clipper will not be able to find
an index key which matches the 15 spaces in the memory buffer and,
therefore, assumes that the index is corrupted.
So, the way to resolve this is to have a "dummy" replace that will force
Clipper to evaluate the index key BEFORE FlexFile deletes the key. The
thing that may be confusing is that Clipper does not care which field is
in the first REPLACE. It is simply a fact that the first REPLACE
evaluates the "before" index key regardless of whether the REPLACE even
affects the index key. It is also a fact that no other index action
occurs until a "commit" type command. At that point the "before" key is
deleted from the index and the "after" key is inserted into the index.
This page created by ng2html v1.05, the Norton guide to HTML conversion utility.
Written by Dave Pearson